package com.apes.fn.business.fixedLoss.repository;

import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.apes.framework.query.ApesDao;
import com.apes.framework.util.DateUtil;
import org.springframework.stereotype.Component;
import org.springframework.util.StringUtils;

import java.text.ParseException;

/**
 * @description: 商务总控相关sql
 * @author: wsj
 * @time: 2021/3/10 16:09
 */
@Component
public class BusinessControlSql extends ApesDao {

    public  JSONArray inLossQuotation() {
        String sql = "SELECT COUNT(A.ID)QUANTITY,'待保险公司出价'NAME,'1' TYPE FROM SER_LOSS_QUOTATION A \n" +
                "WHERE A.STATE IN ('create','techAudit','inquiryFinish')\n" +
                "UNION ALL \n" +
                "SELECT COUNT(A.ID)QUANTITY,'定损已出价未确认' NAME,'2' TYPE FROM SER_LOSS_QUOTATION A\n" +
                "WHERE A.STATE ='confirmPlan'\n";
        return toJSONArray(find(sql));
    }

    public  JSONArray trackData() {
        String sql = "SELECT COUNT(A.ID)QUANTITY,'待后台跟踪' NAME,'1' TYPE FROM SER_CAR_ACCIDENT A WHERE A.STATE = 'backStage'\n" +
                "UNION ALL\n" +
                "SELECT COUNT(A.ID)QUANTITY,'待门店跟进' NAME,'2' TYPE FROM SER_CAR_ACCIDENT A \n" +
                "WHERE A.STATE NOT IN ('backStage','fixedLoss','repeal')";
        return toJSONArray(find(sql));
    }
    public String getWaitQuotationSql() {
        return "SELECT A.ID,DECODE(A.STATE,'create','新增','techAudit','技术审核','inquiryFinish','完成询价')STATE,\n" +
                "       C.CAR_LICENSE CAR_NUMBER,DATE_TIME(A.CREATE_DATE)CREATE_DATE,\n" +
                "       (SELECT D.NAME FROM MAS_DEPT D WHERE A.FIXED_LOSS_DEPT = D.ID)DEPT,\n" +
                "       (SELECT E.NAME FROM HR_EMPLOYEE E WHERE A.LOSS_OFFICER = E.ORIGIN)PERSONNEL,\n" +
                "       (SELECT N.NAME FROM CRM_INSURANCE_COMPANY N WHERE B.INSURANCE_COMPANY_ID = N.ID)INSURANCE_COMPANY\n" +
                "FROM SER_LOSS_QUOTATION A,SER_CAR_ACCIDENT B,FN_CUSTOMER_EXPAND C\n" +
                "WHERE A.STATE IN ('create','techAudit','inquiryFinish')\n" +
                "      AND A.CAR_ACCIDENT = B.ID AND A.CUSTOMER_EXPAND_ID = C.ID";
    }

    public String getUnconfirmedSql() {
        return "SELECT A.ID,'定损已出单' STATE,\n" +
                "       C.CAR_LICENSE CAR_NUMBER,DATE_TIME(A.CREATE_DATE)CREATE_DATE,\n" +
                "       (SELECT D.NAME FROM MAS_DEPT D WHERE A.FIXED_LOSS_DEPT = D.ID)DEPT,\n" +
                "       (SELECT E.NAME FROM HR_EMPLOYEE E WHERE A.LOSS_OFFICER = E.ORIGIN)PERSONNEL,\n" +
                "       (SELECT N.NAME FROM CRM_INSURANCE_COMPANY N WHERE B.INSURANCE_COMPANY_ID = N.ID)INSURANCE_COMPANY\n" +
                "FROM SER_LOSS_QUOTATION A,SER_CAR_ACCIDENT B,FN_CUSTOMER_EXPAND C\n" +
                "WHERE A.STATE ='confirmPlan'\n" +
                "      AND A.CAR_ACCIDENT = B.ID AND A.CUSTOMER_EXPAND_ID = C.ID";
    }


    public String getBackstageTrackSql() {
        return " SELECT A.ID,'转后台' STATE,A.CAR_NUMBER,DATE_TIME(A.CREATE_DATE)CREATE_DATE,\n" +
                "       (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID)DEPT,\n" +
                "       (SELECT NAME FROM CRM_INSURANCE_COMPANY WHERE ID = A.INSURANCE_COMPANY_ID)INSURANCE_COMPANY\n" +
                "FROM SER_CAR_ACCIDENT A WHERE A.STATE = 'backStage' ";
    }


    public String getStoreTrackSql () {
        return " SELECT A.ID,DECODE(A.STATE,'store','转门店','create','新增','baseReported','基地代报案') STATE,\n" +
                "       A.CAR_NUMBER,DATE_TIME(A.CREATE_DATE)CREATE_DATE,\n" +
                "       (SELECT NAME FROM MAS_DEPT WHERE ID = A.DEPT_ID)DEPT,\n" +
                "       (SELECT NAME FROM CRM_INSURANCE_COMPANY WHERE ID = A.INSURANCE_COMPANY_ID)INSURANCE_COMPANY\n" +
                "FROM SER_CAR_ACCIDENT A WHERE A.STATE NOT IN ('backStage','fixedLoss','repeal') ";
    }


    public JSONArray   queryInsuranceSaleData ( JSONObject param) throws ParseException {
        String sql =  "SELECT C.*,\n" +
                "              NVL((SELECT SUM(D.COMMERCIAL_INSURANCE+D.COMPULSORY_INSURANCE)\n" +
                "               FROM INSURANCE_ORDER D WHERE D.STATE IN(4,6,8)\n" +
                "                 AND D.CREATE_DATE >= ADD_MONTHS(TO_DATE(C.STATISTIC_DATE,'YYYY-MM'),-1)\n" +
                "                 AND D.CREATE_DATE < TO_DATE(C.STATISTIC_DATE,'YYYY-MM')\n" +
                "                 AND D.SUPPLIER = C.SUPPLIER),0)LAST_MONTH,\n" +
                "              NVL((SELECT SUM(D.COMMERCIAL_INSURANCE+D.COMPULSORY_INSURANCE)\n" +
                "               FROM INSURANCE_ORDER D WHERE D.STATE IN(4,6,8)\n" +
                "                 AND D.CREATE_DATE >= ADD_MONTHS(TO_DATE(C.STATISTIC_DATE,'YYYY-MM'),-12)\n" +
                "                 AND D.CREATE_DATE < ADD_MONTHS(TO_DATE(C.STATISTIC_DATE,'YYYY-MM'),-11)\n" +
                "                 AND C.SUPPLIER = D.SUPPLIER ),0)LAST_YEAR\n" +
                "            FROM (\n" +
                "            SELECT TO_CHAR(A.CREATE_DATE,'YYYY-MM')STATISTIC_DATE,C.ABBREVIATION,\n" +
                "                  SUM(A.COMMERCIAL_INSURANCE)COMMERCIAL_INSURANCE,A.SUPPLIER,\n" +
                "                   SUM(A.COMPULSORY_INSURANCE)COMPULSORY_INSURANCE,\n" +
                "                   SUM(A.COMMERCIAL_INSURANCE+A.COMPULSORY_INSURANCE)SALE_AMOUNT\n" +
                "            FROM INSURANCE_ORDER A,MAS_PARTY_ROLE B,MAS_PARTY C\n" +
                "            WHERE A.SUPPLIER = B.ID AND B.PARTY_ID = C.ID AND A.STATE IN (4,6,8) ";
        String paramName = "SUPPLIER";
        String dateField = "CREATE_DATE";
        sql = editSqlSting(param, sql,dateField, paramName);
        sql += " GROUP BY  TO_CHAR(A.CREATE_DATE,'YYYY-MM'),C.ABBREVIATION,A.SUPPLIER ) C";
        return toJSONArray(find(sql, param));
    }

    private String editSqlSting(JSONObject param, String sql,String dateField, String paramName )  {
        if (!StringUtils.isEmpty(param.getString("DATE_START"))) {
            param.put("DATE_START", DateUtil.parseUTC(param.getString("DATE_START")));
            sql += " AND A."+dateField+ ">=:DATE_START";
        }
        if (!StringUtils.isEmpty(param.getString("DATE_END"))) {
            param.put("DATE_END",DateUtil.parseUTC(param.getString("DATE_END")));
            sql += " AND A."+dateField+ "<=:DATE_END";
        }
        JSONArray array = new JSONArray();
        if (param.containsKey(paramName)) {
            array = param.getJSONArray(paramName);
            StringBuilder builder = new StringBuilder();
            for (int i = 0, len = array.size(); i < len; i++) {
                JSONObject object = array.getJSONObject(i);
                if (i == 0) {
                    builder.append("'").append(object.getString("id")).append("'");
                } else {
                    builder.append(",'").append(object.getString("id")).append("'");
                }
            }
            if (builder.length()>0){
                sql += " AND A."+paramName +" IN ("+ builder.toString() + ")";
            }
        }
        return sql; 
    }

    public JSONArray queryAutoInsuranceReportData (JSONObject param ) {
        String sql = "SELECT  C.STATISTIC_DATE,C.APP_CODE,SUM(C.TOTAL)TOTAL,\n" +
                "     SUM(C.SETTLEMENT)SETTLEMENT,SUM(C.BACKSTAGE)BACKSTAGE,\n" +
                "     SUM(C.STORE)STORE,SUM(C.REPEAL)REPEAL, " +
                " NVL(SUM((SELECT COUNT(D.ID) FROM SER_CAR_ACCIDENT D WHERE D.STATE = 'fixedLoss'\n" +
                "   AND D.CREATE_DATE >= ADD_MONTHS(TO_DATE(C.STATISTIC_DATE,'YYYY-MM'),-1)\n" +
                "   AND D.CREATE_DATE < TO_DATE(C.STATISTIC_DATE,'YYYY-MM')\n" +
                "   AND D.INSURANCE_COMPANY_ID = C.ID)),0)LAST_MONTH,\n" +
                " NVL(SUM((SELECT COUNT(D.ID) FROM SER_CAR_ACCIDENT D WHERE D.STATE = 'fixedLoss'\n" +
                "   AND D.CREATE_DATE >= ADD_MONTHS(TO_DATE(C.STATISTIC_DATE,'YYYY-MM'),-12)\n" +
                "   AND D.CREATE_DATE < ADD_MONTHS(TO_DATE(C.STATISTIC_DATE,'YYYY-MM'),-11)\n" +
                "   AND C.ID = D.INSURANCE_COMPANY_ID )),0)LAST_YEAR\n" +
                " FROM (\n" +
                "  SELECT TO_CHAR(A.CREATE_DATE,'YYYY-MM')STATISTIC_DATE,B.APP_CODE,B.ID,\n" +
                "         COUNT(A.ID)TOTAL,SUM(CASE WHEN A.STATE = 'fixedLoss' THEN 1 ELSE 0 END)SETTLEMENT,\n" +
                "         SUM(CASE WHEN A.STATE = 'store' THEN 1 ELSE 0 END)STORE,\n" +
                "         SUM(CASE WHEN A.STATE = 'backStage' THEN 1 ELSE 0 END)BACKSTAGE," +
                "         SUM(CASE WHEN A.STATE = 'repeal' THEN 1 ELSE 0 END)REPEAL "+
                "   FROM SER_CAR_ACCIDENT A,CRM_INSURANCE_COMPANY B \n" +
                "  WHERE A.INSURANCE_COMPANY_ID =B.ID \n" ;
        String paramName = "INSURANCE_COMPANY_ID";
        String dateField = "CREATE_DATE";
        sql = editSqlSting(param, sql,dateField, paramName);

        sql += "  GROUP BY  TO_CHAR(A.CREATE_DATE,'YYYY-MM'),B.APP_CODE,B.ID) C\n" +
                " GROUP BY C.STATISTIC_DATE,C.APP_CODE " +
                " ORDER BY C.STATISTIC_DATE ASC";
        return toJSONArray(find(sql, param));
    }

    public JSONArray queryBusinessApproveData(JSONObject param) {
        String sql ="SELECT TO_CHAR(A.APPROVE_DATE,'YYYY-MM')STATISTIC_DATE,COUNT(A.ID)ORDER_NUMBER,\n" +
                "      ROUND(SUM(((A.APPROVE_DATE+0)-(A.TO_BUSINESS_DATE+0))* 24*60),0)CONSUME_TIME\n" +
                " FROM SER_LOSS_QUOTATION A WHERE A.STATE = 'businessAudit' ";
        if (!StringUtils.isEmpty(param.getString("DATE_START"))) {
            param.put("DATE_START", DateUtil.parseUTC(param.getString("DATE_START")));
            sql += " AND A.APPROVE_DATE >=:DATE_START";
        }
        if (!StringUtils.isEmpty(param.getString("DATE_END"))) {
            param.put("DATE_END",DateUtil.parseUTC(param.getString("DATE_END")));
            sql += " AND A.APPROVE_DATE <=:DATE_END ";
        }
        sql += " GROUP BY TO_CHAR(A.APPROVE_DATE,'YYYY-MM') ORDER BY TO_CHAR(A.APPROVE_DATE,'YYYY-MM') ASC";
        return toJSONArray(find((sql), param));
    }


    public JSONArray queryInsuranceSettlementClaimData (JSONObject param ) {
        String sql = "SELECT D.*,\n" +
                "   NVL((SELECT SUM(E.INSURANCE_OFFER) FROM SER_LOSS_QUOTATION E \n" +
                "            WHERE E.STATE = 'businessAudit'\n" +
                "               AND E.APPROVE_DATE >= ADD_MONTHS(TO_DATE(D.STATISTIC_DATE,'YYYY-MM'),-1)\n" +
                "               AND E.APPROVE_DATE < TO_DATE(D.STATISTIC_DATE,'YYYY-MM')\n" +
                "               AND E.INSURANCE_COMPANY_ID = D.ID),0)LAST_MONTH,\n" +
                "   NVL((SELECT SUM(E.INSURANCE_OFFER) FROM SER_LOSS_QUOTATION E WHERE E.STATE = 'businessAudit'\n" +
                "     AND E.APPROVE_DATE >= ADD_MONTHS(TO_DATE(D.STATISTIC_DATE,'YYYY-MM'),-12)\n" +
                "     AND E.APPROVE_DATE < ADD_MONTHS(TO_DATE(D.STATISTIC_DATE,'YYYY-MM'),-11)\n" +
                "     AND D.ID = E.INSURANCE_COMPANY_ID),0)LAST_YEAR,\n" +
                "  (SELECT SUM(E.INSURANCE_OFFER) FROM SER_LOSS_QUOTATION E \n" +
                "   WHERE E.STATE = 'businessAudit' AND TRUNC(E.APPROVE_DATE,'MM') = TO_DATE(D.STATISTIC_DATE,'YYYY-MM'))TOTAL_AMOUNT   \n" +
                " FROM (\n" +
                "  SELECT TO_CHAR(A.APPROVE_DATE,'YYYY-MM')STATISTIC_DATE,C.NAME,C.ID,\n" +
                "         SUM(B.FIXED_LOSS_AMOUNT)FIXED_LOSS_AMOUNT,\n" +
                "         SUM(B.MIN_FIXED_LOSS_AMOUNT)PRICE_COST_UNIT_IN_TAX\n" +
                "  FROM SER_LOSS_QUOTATION A,SER_LOSS_QUOTATION_PLAN B,CRM_INSURANCE_COMPANY C\n" +
                "  WHERE A.ID = B.LOSS_QUOTATION_ID AND A.INSURANCE_COMPANY_ID = C.ID\n" +
                "   AND A.STATE = 'businessAudit' " ;
        String paramName = "INSURANCE_COMPANY_ID";
        String dateField = "APPROVE_DATE";
        sql = editSqlSting(param, sql, dateField,paramName);

        sql += "  GROUP BY TO_CHAR(A.APPROVE_DATE,'YYYY-MM'),C.NAME,C.ID ) D " +
                " ORDER BY D.STATISTIC_DATE ASC";
        return toJSONArray(find(sql, param));
    }

    public  JSONArray unsettledData() {
        String sql = "SELECT COUNT(A.ID)QUANTITY,'未结算单据' NAME,'1' TYPE  FROM SER_SERVICE_PLAN A " +
                " WHERE A.STATE = 'qualityTest' AND A.QUALITY_TEST_DATE <= SYSDATE -3";
        return toJSONArray(find(sql));
    }


    public  JSONArray unsettledDetailData() {
        String sql = "SELECT A.ID,A.SALES_AMOUNT,A.SOURCE_BUSINESS_TYPE_NAME,A.SOURCE_FROM,A.GET_CAR_TYPE,\n" +
                "       DATE_TIME(A.CREATE_DATE)CREATE_DATE,DATE_TIME(A.SUBMISSION_DATE)SUBMISSION_DATE," +
                "       DATE_TIME(A.CONFIRM_DATE)CONFIRM_DATE,DATE_TIME(A.QUALITY_TEST_DATE)QUALITY_TEST_DATE,A.REMARK,\n" +
                "       (SELECT NAME FROM MAS_DEPT WHERE ID = A.BELONG_DEPT_ID)DEPT,'质检' STATE," +
                "       (SELECT SUM(K.AMOUNT) FROM ADVANCE_PAYMENT K WHERE K.SOURCE = A.ID)DEPOSIT,\n" +
                "       (SELECT CAR_LICENSE FROM FN_CUSTOMER_EXPAND WHERE ID = A.FN_CUSTOMER_EXPAND_ID)CAR_LICENSE\n" +
                "FROM SER_SERVICE_PLAN A WHERE A.STATE = 'qualityTest' AND A.QUALITY_TEST_DATE <= SYSDATE -3";
        return toJSONArray(find(sql));
    }
}
